﻿using System;
using System.Linq;
using System.Data.Linq;

namespace UniMana.Data
{
    public static class Catalogue_TrainingTypeData
    {
        /// <summary>
        /// Lấy loại hình đào tạo với khoa
        /// </summary>
        /// <param name="nTrainingType"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iDeapartment"></param>
        /// <returns></returns>

        public static IQueryable GetTrainingTypeByDepartment(this Table<Catalogue_TrainingType> nCatalogue_TrainingType,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iDeapartment)
        {
            var trainingtype = (from t in nTraining_TrainingProgram
                                join t0 in nCatalogue_Major on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                                join t1 in nCatalogue_TrainingType on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t1.Catalogue_TrainingTypeKey }
                                where
                                  t0.Catalogue_DepartmentKey == m_iDeapartment
                                select new
                                {
                                    Catalogue_TrainingTypeKey = (System.Int32?)t.Catalogue_TrainingTypeKey,
                                    t1.Name,
                                }).Distinct();
            return trainingtype.Distinct();
        }

        /// <summary>
        /// Lấy loại hình đào tạo với bậc đào tạo
        /// </summary>
        /// <param name="nTrainingType"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iLevel"></param>
        /// <returns></returns>

        public static IQueryable GetTrainingTypeByLevel(this Table<Catalogue_TrainingType> nCatalogue_TrainingType,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iLevel)
        {
            var trainingtype = (from t in nTraining_TrainingProgram
                                join t0 in nCatalogue_Major on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                                join t1 in nCatalogue_TrainingType on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t1.Catalogue_TrainingTypeKey }
                                where
                                  t.Catalogue_LevelKey == m_iLevel
                                select new
                                {
                                    Catalogue_TrainingTypeKey = (System.Int32?)t.Catalogue_TrainingTypeKey,
                                    t1.Name,
                                }).Distinct();
                return trainingtype.Distinct();
        }

        /// <summary>
        /// Lấy loại hình đào tạo với ngành
        /// </summary>
        /// <param name="nTrainingType"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iMajor"></param>
        /// <returns></returns>

        public static IQueryable GetTrainingTypeByMajor(this Table<Catalogue_TrainingType> nCatalogue_TrainingType,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iMajor)
        {
            var trainingtype = (from t in nTraining_TrainingProgram
                                join t0 in nCatalogue_Major on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                                join t1 in nCatalogue_TrainingType on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t1.Catalogue_TrainingTypeKey }
                                where
                                  t.Catalogue_MajorKey == m_iMajor
                                select new
                                {
                                    Catalogue_TrainingTypeKey = (System.Int32?)t.Catalogue_TrainingTypeKey,
                                    t1.Name,
                                }).Distinct();
            return trainingtype.Distinct();
        }

        /// <summary>
        /// Lấy loại hình đào tạo với khóa học
        /// </summary>
        /// <param name="nTrainingType"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>

        public static IQueryable GetTrainingTypeByCourse(this Table<Catalogue_TrainingType> nCatalogue_TrainingType,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iCourse)
        {
            var trainingtype = (from t in nTraining_TrainingProgram
                                join t0 in nCatalogue_Major on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                                join t1 in nCatalogue_TrainingType on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t1.Catalogue_TrainingTypeKey }
                                where
                                  t.Catalogue_CourseKey == m_iCourse
                                select new
                                {
                                    Catalogue_TrainingTypeKey = (System.Int32?)t.Catalogue_TrainingTypeKey,
                                    t1.Name,
                                }).Distinct();
            return trainingtype.Distinct();
        }

        /// <summary>
        /// Lấy loại hình đào tạo có trong chương trình đào tạo
        /// </summary>
        /// <param name="nTrainingType"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <returns></returns>

        public static IQueryable GetTrainingTypeInTraingProgram(this Table<Catalogue_TrainingType> nCatalogue_TrainingType,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major)
        {
            var trainingprogram = (from t in nTraining_TrainingProgram
                                   join t0 in nCatalogue_Major on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                                   join t1 in nCatalogue_TrainingType on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t1.Catalogue_TrainingTypeKey }
                                   select new
                                   {
                                       Catalogue_TrainingTypeKey = (System.Int32?)t.Catalogue_TrainingTypeKey,
                                       t1.Name
                                   }).Distinct();
            return trainingprogram.Distinct();
        }

        /// <summary>
        /// Lấy loại hình với khoa, bậc đào tạo, nga , khóa học
        /// </summary>
        /// <param name="nTrainingType"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="lueCatalogueDepartment"></param>
        /// <param name="lueCatalogueLevel"></param>
        /// <param name="lueCatalogueMajor"></param>
        /// <param name="lueCatalogueCourse"></param>
        /// <returns></returns>

        public static IQueryable GetTrainingTypeByDepartmentLevelMajorCourse(this Table<Catalogue_TrainingType> nCatalogue_TrainingType,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iDepartment,
            int m_iLevel,
            int m_iMajor,
            int m_iCourse)
        {
            var trainingType = from t in nTraining_TrainingProgram
                               join t0 in nCatalogue_Major on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                               join t1 in nCatalogue_TrainingType on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t1.Catalogue_TrainingTypeKey }
                               select new
                               {
                                   t.Catalogue_LevelKey,
                                   Catalogue_MajorKey = (System.Int32?)t.Catalogue_MajorKey,
                                   Catalogue_TrainingTypeKey = (System.Int32?)t1.Catalogue_TrainingTypeKey,
                                   t.Catalogue_CourseKey,
                                   t0.Catalogue_DepartmentKey,
                                   t1.Name
                               };
            if (m_iDepartment > 0)
            {
                trainingType = trainingType.Where(p => p.Catalogue_DepartmentKey == int.Parse(m_iDepartment.ToString()));
            }
            if (m_iLevel > 0)
            {
                trainingType = trainingType.Where(p => p.Catalogue_LevelKey == int.Parse(m_iLevel.ToString()));
            }
            if (m_iMajor > 0)
            {
                trainingType = trainingType.Where(p => p.Catalogue_MajorKey == int.Parse(m_iMajor.ToString()));
            }
            if (m_iCourse > 0)
            {
                trainingType = trainingType.Where(p => p.Catalogue_CourseKey == int.Parse(m_iCourse.ToString()));
            }

            var a = from type in trainingType
                    select new
                    {
                        type.Catalogue_TrainingTypeKey,
                        type.Name
                    };
            return a.Distinct();
        }

        /// <summary>
        /// Lấy tên loại hình với mã
        /// </summary>
        /// <param name="nCatalogue_TrainingType"></param>
        /// <param name="m_iTypeKey"></param>
        /// <returns></returns>

        public static string ReturnNameWithKey(this Table<Catalogue_TrainingType> nCatalogue_TrainingType,
            int m_iKey)
        {
            var values = (from t in nCatalogue_TrainingType
                          where t.Catalogue_TrainingTypeKey == m_iKey
                          select new
                          {
                              t.Name
                          }).Distinct().FirstOrDefault();
            return values.Name.ToString();
        }
    }
}
